#-*-coding: UTF-8 -*-

import paho.mqtt.client as mqtt
import mysql.connector
import time
import requests

MQTT_BROKER_URL = "199.247.17.178"
MQTT_BROKER_PORT = 1883
MQTT_TIMEOUT = 60
MQTT_TOPIC = "/CO2/#"
URL = "http://localhost/phpweb3/esbc_public/sql2bc.php"

def on_connect(client, userdata, flags, rc):
    print("Connected with result code "+str(rc))
    client.subscribe(MQTT_TOPIC)

def on_message(client, userdata, msg):
    print(msg.topic+":"+str(msg.payload))
    topic=msg.topic
    payload=msg.payload.decode() 
    db = mysql.connector.connect(
       host="localhost",
       user="root",
       passwd="sweetekhappy",
       database="esbc_mqtt"
    )
    cursor = db.cursor()
    timenow=time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())
    try:
        sqlquery = "INSERT INTO `mqtt_raw` (mqtt_topic,mqtt_msg,add_date) VALUES (%s,%s,%s)"
        #print(sqlquery)
        val = (topic,payload,timenow)
        print(val)
        cursor.execute(sqlquery,val)
        db.commit() #commit the insert
        print("insert OK")
        cursor.close()  #close the cursor
        r = requests.get(URL)
        #r.text
        print(r.text)
    except MySQLdb.IntegrityError:
        print("failed to insert data")
    finally:
        cursor.close()  #close just incase it failed


client = mqtt.Client()
client.on_connect = on_connect
client.on_message = on_message

client.connect(MQTT_BROKER_URL, MQTT_BROKER_PORT, MQTT_TIMEOUT)

client.loop_forever()
